const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const crypto = require('crypto');
const { v4: uuidv4 } = require('uuid');

class Database {
    constructor() {
        this.db = null;
        this.isInitialized = false;
    }

    // 初始化数据库
    async initialize(dbPath = './logs.db') {
        return new Promise((resolve, reject) => {
            // 确保数据库目录存在
            const dbDir = path.dirname(dbPath);
            if (!fs.existsSync(dbDir)) {
                fs.mkdirSync(dbDir, { recursive: true });
            }

            // 创建数据库连接
            this.db = new sqlite3.Database(dbPath, (err) => {
                if (err) {
                    console.error('数据库连接失败:', err.message);
                    reject(err);
                    return;
                }
                console.log('已连接到SQLite数据库');
                this.createTables().then(() => {
                    this.isInitialized = true;
                    resolve();
                }).catch(reject);
            });
        });
    }

    // 创建表结构
    async createTables() {
        return new Promise((resolve, reject) => {
            const createLogsTable = `
                CREATE TABLE IF NOT EXISTS logs (
                    id TEXT PRIMARY KEY,
                    level TEXT NOT NULL,
                    module TEXT,
                    message TEXT NOT NULL,
                    timestamp DATETIME NOT NULL,
                    client_id TEXT,
                    device_id TEXT,
                    session_id TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (device_id) REFERENCES devices(id),
                    FOREIGN KEY (session_id) REFERENCES sessions(id)
                )
            `;
            
            // 创建设备表
            const createDevicesTable = `
                CREATE TABLE IF NOT EXISTS devices (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT,
                    first_connected DATETIME NOT NULL,
                    last_connected DATETIME NOT NULL,
                    is_active BOOLEAN DEFAULT 1,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `;

            // 创建会话表
            const createSessionsTable = `
                CREATE TABLE IF NOT EXISTS sessions (
                    id TEXT PRIMARY KEY,
                    device_id TEXT NOT NULL,
                    session_name TEXT,
                    start_time DATETIME NOT NULL,
                    end_time DATETIME,
                    is_active BOOLEAN DEFAULT 1,
                    log_count INTEGER DEFAULT 0,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (device_id) REFERENCES devices(id)
                )
            `;

            // 创建去重日志表，用于优化重复日志存储
            const createDeduplicatedLogsTable = `
                CREATE TABLE IF NOT EXISTS deduplicated_logs (
                    id TEXT PRIMARY KEY,
                    level TEXT NOT NULL,
                    module TEXT,
                    message TEXT NOT NULL,
                    message_hash TEXT NOT NULL UNIQUE,
                    first_timestamp DATETIME NOT NULL,
                    last_timestamp DATETIME NOT NULL,
                    count INTEGER DEFAULT 1,
                    client_id TEXT,
                    device_id TEXT,
                    session_id TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (device_id) REFERENCES devices(id),
                    FOREIGN KEY (session_id) REFERENCES sessions(id)
                )
            `;

            const createIndexes = [
                // 日志表索引
                'CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp)',
                'CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level)',
                'CREATE INDEX IF NOT EXISTS idx_logs_module ON logs(module)',
                'CREATE INDEX IF NOT EXISTS idx_logs_client_id ON logs(client_id)',
                'CREATE INDEX IF NOT EXISTS idx_logs_device_id ON logs(device_id)',
                'CREATE INDEX IF NOT EXISTS idx_logs_session_id ON logs(session_id)',
                'CREATE INDEX IF NOT EXISTS idx_logs_created_at ON logs(created_at)',
                // 设备表索引
                'CREATE INDEX IF NOT EXISTS idx_devices_name ON devices(name)',
                'CREATE INDEX IF NOT EXISTS idx_devices_is_active ON devices(is_active)',
                'CREATE INDEX IF NOT EXISTS idx_devices_last_connected ON devices(last_connected)',
                // 会话表索引
                'CREATE INDEX IF NOT EXISTS idx_sessions_device_id ON sessions(device_id)',
                'CREATE INDEX IF NOT EXISTS idx_sessions_is_active ON sessions(is_active)',
                'CREATE INDEX IF NOT EXISTS idx_sessions_start_time ON sessions(start_time)',
                // 去重表索引
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_message_hash ON deduplicated_logs(message_hash)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_timestamp ON deduplicated_logs(last_timestamp)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_level ON deduplicated_logs(level)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_module ON deduplicated_logs(module)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_count ON deduplicated_logs(count)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_device_id ON deduplicated_logs(device_id)',
                'CREATE INDEX IF NOT EXISTS idx_dedup_logs_session_id ON deduplicated_logs(session_id)'
            ];

            // 创建设备表
            this.db.run(createDevicesTable, (err) => {
                if (err) {
                    console.error('创建devices表失败:', err.message);
                    reject(err);
                    return;
                }
                
                // 创建会话表
                this.db.run(createSessionsTable, (err) => {
                    if (err) {
                        console.error('创建sessions表失败:', err.message);
                        reject(err);
                        return;
                    }
                    
                    // 创建日志表
                    this.db.run(createLogsTable, (err) => {
                        if (err) {
                            console.error('创建logs表失败:', err.message);
                            reject(err);
                            return;
                        }
                        
                        // 创建去重日志表
                         this.db.run(createDeduplicatedLogsTable, (err) => {
                             if (err) {
                                 console.error('创建deduplicated_logs表失败:', err.message);
                                 reject(err);
                                 return;
                             }

                             // 创建索引
                             let indexCount = 0;
                             const totalIndexes = createIndexes.length;

                             createIndexes.forEach((indexSql) => {
                                 this.db.run(indexSql, (err) => {
                                     if (err) {
                                         console.error('创建索引失败:', err.message);
                                     }
                                     indexCount++;
                                     if (indexCount === totalIndexes) {
                                         console.log('数据库表和索引创建完成');
                                         resolve();
                                     }
                                 });
                             });
                         });
                     });
                 });
             });
        });
    }

    // 生成消息哈希（用于去重）
    generateMessageHash(level, module, message) {
        const content = `${level}:${module || ''}:${message}`;
        return crypto.createHash('md5').update(content).digest('hex');
    }
    
    // 插入去重日志
    async insertDeduplicatedLog(logData, clientId = null, deviceId = null, sessionId = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const messageHash = this.generateMessageHash(logData.level, logData.module, logData.message);
            const dbRef = this.db; // 保存数据库引用到外层作用域
            
            // 首先检查是否已存在相同的日志
            const checkSql = 'SELECT id, count FROM deduplicated_logs WHERE message_hash = ?';
            
            dbRef.get(checkSql, [messageHash], (err, row) => {
                if (err) {
                    console.error('检查重复日志失败:', err.message);
                    reject(err);
                    return;
                }
                
                if (row) {
                    // 存在重复日志，更新计数和最后时间戳
                    const updateSql = `
                        UPDATE deduplicated_logs 
                        SET count = count + 1, 
                            last_timestamp = ?, 
                            updated_at = CURRENT_TIMESTAMP
                        WHERE id = ?
                    `;
                    
                    dbRef.run(updateSql, [logData.timestamp || new Date().toISOString(), row.id], function(err) {
                        if (err) {
                            console.error('更新重复日志失败:', err.message);
                            reject(err);
                            return;
                        }
                        resolve({ 
                            id: row.id, 
                            isDuplicate: true, 
                            newCount: row.count + 1,
                            rowId: this.lastID 
                        });
                    });
                } else {
                    // 新日志，插入到去重表
                    const id = uuidv4();
                    const insertSql = `
                        INSERT INTO deduplicated_logs 
                        (id, level, module, message, message_hash, first_timestamp, last_timestamp, count, client_id, device_id, session_id)
                        VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?)
                    `;
                    
                    const timestamp = logData.timestamp || new Date().toISOString();
                    const params = [
                        id,
                        logData.level,
                        logData.module || null,
                        logData.message,
                        messageHash,
                        timestamp,
                        timestamp,
                        clientId,
                        deviceId,
                        sessionId
                    ];
                    
                    dbRef.run(insertSql, params, function(err) {
                        if (err) {
                            // 如果是UNIQUE约束失败，说明在并发情况下已经插入了相同的日志
                            if (err.message.includes('UNIQUE constraint failed')) {
                                 // 重新查询并更新计数
                                 const recheckSql = 'SELECT id, count FROM deduplicated_logs WHERE message_hash = ?';
                                 dbRef.get(recheckSql, [messageHash], (recheckErr, recheckRow) => {
                                    if (recheckErr) {
                                        console.error('重新检查重复日志失败:', recheckErr.message);
                                        reject(recheckErr);
                                        return;
                                    }
                                    if (recheckRow) {
                                        // 更新计数
                                        const updateSql = `
                                            UPDATE deduplicated_logs 
                                            SET count = count + 1, 
                                                last_timestamp = ?, 
                                                updated_at = CURRENT_TIMESTAMP
                                            WHERE id = ?
                                        `;
                                        dbRef.run(updateSql, [timestamp, recheckRow.id], function(updateErr) {
                                            if (updateErr) {
                                                console.error('更新重复日志计数失败:', updateErr.message);
                                                reject(updateErr);
                                                return;
                                            }
                                            resolve({ 
                                                id: recheckRow.id, 
                                                isDuplicate: true, 
                                                newCount: recheckRow.count + 1,
                                                rowId: this.lastID 
                                            });
                                        });
                                    } else {
                                        console.error('插入去重日志失败:', err.message);
                                        reject(err);
                                    }
                                });
                            } else {
                                console.error('插入去重日志失败:', err.message);
                                reject(err);
                            }
                            return;
                        }
                        resolve({ 
                            id, 
                            isDuplicate: false, 
                            newCount: 1,
                            rowId: this.lastID 
                        });
                    });
                }
            });
        });
    }

    // 插入日志（保留原有方法用于兼容性）
    async insertLog(logData, clientId = null, deviceId = null, sessionId = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const id = uuidv4();
            const sql = `
                INSERT INTO logs (id, level, module, message, timestamp, client_id, device_id, session_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `;
            
            const params = [
                id,
                logData.level,
                logData.module || null,
                logData.message,
                logData.timestamp || new Date().toISOString(),
                clientId,
                deviceId,
                sessionId
            ];

            this.db.run(sql, params, function(err) {
                if (err) {
                    console.error('插入日志失败:', err.message);
                    reject(err);
                    return;
                }
                resolve({ id, rowId: this.lastID });
            });
        });
    }

    // 批量插入去重日志
    async insertDeduplicatedLogsBatch(logsArray, clientId = null, deviceId = null, sessionId = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        if (!Array.isArray(logsArray) || logsArray.length === 0) {
            return { success: true, insertedCount: 0, duplicateCount: 0 };
        }

        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');
                
                let insertedCount = 0;
                let duplicateCount = 0;
                let processedCount = 0;
                let hasError = false;
                
                const processLog = async (logData) => {
                    try {
                        const result = await this.insertDeduplicatedLog(logData, clientId, deviceId, sessionId);
                        if (result.isDuplicate) {
                            duplicateCount++;
                        } else {
                            insertedCount++;
                        }
                    } catch (err) {
                        console.error('批量插入去重日志项失败:', err.message);
                        hasError = true;
                    }
                    
                    processedCount++;
                    if (processedCount === logsArray.length) {
                        if (hasError) {
                            this.db.run('ROLLBACK', () => {
                                reject(new Error('批量插入去重日志失败'));
                            });
                        } else {
                            this.db.run('COMMIT', (err) => {
                                if (err) {
                                    reject(err);
                                } else {
                                    resolve({ success: true, insertedCount, duplicateCount });
                                }
                            });
                        }
                    }
                };
                
                // 处理每个日志
                logsArray.forEach(processLog);
            });
        });
    }

    // 批量插入日志（提高性能）
    async insertLogsBatch(logs, clientId = null, deviceId = null, sessionId = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const sql = `
                INSERT INTO logs (id, level, module, message, timestamp, client_id, device_id, session_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `;

            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');

                const stmt = this.db.prepare(sql);
                const results = [];

                logs.forEach((logData) => {
                    const id = uuidv4();
                    const params = [
                        id,
                        logData.level,
                        logData.module || null,
                        logData.message,
                        logData.timestamp || new Date().toISOString(),
                        clientId,
                        deviceId,
                        sessionId
                    ];

                    stmt.run(params, function(err) {
                        if (err) {
                            console.error('批量插入日志失败:', err.message);
                        } else {
                            results.push({ id, rowId: this.lastID });
                        }
                    });
                });

                stmt.finalize((err) => {
                    if (err) {
                        this.db.run('ROLLBACK');
                        reject(err);
                        return;
                    }

                    this.db.run('COMMIT', (err) => {
                        if (err) {
                            reject(err);
                        } else {
                            resolve(results);
                        }
                    });
                });
            });
        });
    }

    // 查询去重日志（支持分页和过滤）
    async queryDeduplicatedLogs(options = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                page = 1,
                pageSize = 100,
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                clientId = null
            } = options;

            let sql = 'SELECT * FROM deduplicated_logs WHERE 1=1';
            const params = [];

            // 添加过滤条件
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND last_timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND last_timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (deviceId) {
                sql += ' AND device_id = ?';
                params.push(deviceId);
            }

            if (sessionId) {
                sql += ' AND session_id = ?';
                params.push(sessionId);
            }

            // 添加排序和分页
            sql += ' ORDER BY last_timestamp DESC';
            sql += ' LIMIT ? OFFSET ?';
            params.push(pageSize, (page - 1) * pageSize);

            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('查询去重日志失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(rows);
            });
        });
    }

    // 查询日志（支持分页和过滤）
    async queryLogs(options = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                page = 1,
                pageSize = 100,
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                clientId = null
            } = options;

            let sql = 'SELECT * FROM logs WHERE 1=1';
            const params = [];

            // 添加过滤条件
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (deviceId) {
                sql += ' AND device_id = ?';
                params.push(deviceId);
            }

            if (sessionId) {
                sql += ' AND session_id = ?';
                params.push(sessionId);
            }

            // 添加排序和分页
            sql += ' ORDER BY timestamp DESC';
            sql += ' LIMIT ? OFFSET ?';
            params.push(pageSize, (page - 1) * pageSize);

            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('查询日志失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(rows);
            });
        });
    }

    // 获取去重日志（支持分页和过滤）
    async getDeduplicatedLogs(limit = 50, offset = 0, filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                deviceId = null,
                sessionId = null,
                clientId = null
            } = filters;

            let sql = 'SELECT * FROM deduplicated_logs WHERE 1=1';
            const params = [];

            // 添加过滤条件
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND last_timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND last_timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (deviceId) {
                sql += ' AND device_id = ?';
                params.push(deviceId);
            }

            if (sessionId) {
                sql += ' AND session_id = ?';
                params.push(sessionId);
            }

            // 添加排序和分页
            sql += ' ORDER BY last_timestamp DESC';
            sql += ' LIMIT ? OFFSET ?';
            params.push(limit, offset);

            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('获取去重日志失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(rows);
            });
        });
    }

    // 获取原始日志（支持分页和过滤）
    async getLogs(limit = 50, offset = 0, filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                deviceId = null,
                sessionId = null
            } = filters;

            let sql = 'SELECT * FROM logs WHERE 1=1';
            const params = [];

            // 添加过滤条件
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (deviceId) {
                sql += ' AND device_id = ?';
                params.push(deviceId);
            }

            if (sessionId) {
                sql += ' AND session_id = ?';
                params.push(sessionId);
            }

            // 添加排序和分页
            sql += ' ORDER BY timestamp DESC';
            sql += ' LIMIT ? OFFSET ?';
            params.push(limit, offset);

            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('获取日志失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(rows);
            });
        });
    }

    // 获取去重日志总数
    async getDeduplicatedLogCount(filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                clientId = null
            } = options;

            let sql = 'SELECT COUNT(*) as count FROM deduplicated_logs WHERE 1=1';
            const params = [];

            // 添加过滤条件
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND last_timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND last_timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (clientId) {
                sql += ' AND client_id = ?';
                params.push(clientId);
            }

            this.db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取去重日志总数失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(row.count);
            });
        });
    }

    // 获取日志总数
    async getLogCount(filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const {
                level = null,
                module = null,
                startTime = null,
                endTime = null,
                search = null,
                deviceId = null,
                sessionId = null
            } = filters;

            let sql = 'SELECT COUNT(*) as count FROM logs WHERE 1=1';
            const params = [];

            // 添加过滤条件（与queryLogs相同）
            if (level) {
                sql += ' AND level = ?';
                params.push(level);
            }

            if (module) {
                sql += ' AND module = ?';
                params.push(module);
            }

            if (startTime) {
                sql += ' AND timestamp >= ?';
                params.push(startTime);
            }

            if (endTime) {
                sql += ' AND timestamp <= ?';
                params.push(endTime);
            }

            if (search) {
                sql += ' AND message LIKE ?';
                params.push(`%${search}%`);
            }

            if (clientId) {
                sql += ' AND client_id = ?';
                params.push(clientId);
            }

            this.db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取日志总数失败:', err.message);
                    reject(err);
                    return;
                }
                resolve(row.count);
            });
        });
    }

    // 获取最近的日志（用于新连接的客户端）
    async getRecentLogs(limit = 100) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const sql = 'SELECT * FROM logs ORDER BY timestamp DESC LIMIT ?';
            
            this.db.all(sql, [limit], (err, rows) => {
                if (err) {
                    console.error('获取最近日志失败:', err.message);
                    reject(err);
                    return;
                }
                // 反转数组，使最旧的日志在前面
                resolve(rows.reverse());
            });
        });
    }

    // 清理旧日志（保留指定天数，包括去重日志）
    async cleanupOldLogs(daysToKeep = 30) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const cutoffDate = new Date();
            cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);
            const cutoffTimestamp = cutoffDate.toISOString();

            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');
                
                let totalCleaned = 0;
                
                // 清理原始日志表
                const logsSql = 'DELETE FROM logs WHERE created_at < ?';
                this.db.run(logsSql, [cutoffTimestamp], function(err) {
                    if (err) {
                        this.db.run('ROLLBACK');
                        console.error('清理旧原始日志失败:', err.message);
                        reject(err);
                        return;
                    }
                    
                    totalCleaned += this.changes;
                    
                    // 清理去重日志表
                    const deduplicatedLogsSql = 'DELETE FROM deduplicated_logs WHERE created_at < ?';
                    this.db.run(deduplicatedLogsSql, [cutoffTimestamp], function(err) {
                        if (err) {
                            this.db.run('ROLLBACK');
                            console.error('清理旧去重日志失败:', err.message);
                            reject(err);
                            return;
                        }
                        
                        totalCleaned += this.changes;
                        
                        this.db.run('COMMIT', (err) => {
                            if (err) {
                                reject(err);
                            } else {
                                console.log(`清理了 ${totalCleaned} 条超过 ${daysToKeep} 天的旧日志（包括去重日志）`);
                                resolve(totalCleaned);
                            }
                        });
                    });
                });
            });
        });
    }

    // 清除所有日志（包括去重日志）
    async clearAllLogs() {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');
                
                // 清除原始日志表
                this.db.run('DELETE FROM logs', (err) => {
                    if (err) {
                        this.db.run('ROLLBACK');
                        console.error('清除原始日志失败:', err.message);
                        reject(err);
                        return;
                    }
                    
                    // 清除去重日志表
                    this.db.run('DELETE FROM deduplicated_logs', (err) => {
                        if (err) {
                            this.db.run('ROLLBACK');
                            console.error('清除去重日志失败:', err.message);
                            reject(err);
                            return;
                        }
                        
                        // 清除历史会话记录
                        this.db.run('DELETE FROM sessions', (err) => {
                            if (err) {
                                this.db.run('ROLLBACK');
                                console.error('清除历史会话失败:', err.message);
                                reject(err);
                                return;
                            }
                            
                            this.db.run('COMMIT', (err) => {
                                if (err) {
                                    reject(err);
                                } else {
                                    console.log('所有日志和历史会话已清除');
                                    resolve();
                                }
                            });
                        });
                    });
                });
            });
        });
    }

    // 清除所有会话记录
    async clearAllSessions() {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');
                
                // 清除会话表
                this.db.run('DELETE FROM sessions', (err) => {
                    if (err) {
                        this.db.run('ROLLBACK');
                        console.error('清除会话记录失败:', err.message);
                        reject(err);
                        return;
                    }
                    
                    this.db.run('COMMIT', (err) => {
                        if (err) {
                            reject(err);
                        } else {
                            console.log('所有会话记录已清除');
                            resolve();
                        }
                    });
                });
            });
        });
    }

    // 获取去重日志统计信息
    async getDeduplicatedLogStats(filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            let whereClause = 'WHERE 1=1';
            const params = [];
            
            if (filters.startTime) {
                whereClause += ' AND last_timestamp >= ?';
                params.push(filters.startTime);
            }
            
            if (filters.endTime) {
                whereClause += ' AND last_timestamp <= ?';
                params.push(filters.endTime);
            }

            // 按级别统计（包含重复次数）
            const levelStatsSql = `
                SELECT level, COUNT(*) as unique_count, SUM(count) as total_count
                FROM deduplicated_logs
                ${whereClause}
                GROUP BY level
                ORDER BY level
            `;

            this.db.all(levelStatsSql, params, (err, levelStats) => {
                if (err) {
                    console.error('获取去重日志级别统计失败:', err.message);
                    reject(err);
                    return;
                }

                // 按模块统计（包含重复次数）
                const moduleStatsSql = `
                    SELECT module, COUNT(*) as unique_count, SUM(count) as total_count
                    FROM deduplicated_logs
                    ${whereClause}
                    GROUP BY module
                    ORDER BY total_count DESC
                    LIMIT 10
                `;

                this.db.all(moduleStatsSql, params, (err, moduleStats) => {
                    if (err) {
                        console.error('获取去重日志模块统计失败:', err.message);
                        reject(err);
                        return;
                    }

                    // 总数统计
                    const totalSql = `
                        SELECT COUNT(*) as unique_total, SUM(count) as total
                        FROM deduplicated_logs
                        ${whereClause}
                    `;

                    this.db.get(totalSql, params, (err, totalResult) => {
                        if (err) {
                            console.error('获取去重日志总数统计失败:', err.message);
                            reject(err);
                            return;
                        }

                        // 按小时统计（最近24小时）
                        const hourlyStatsSql = `
                            SELECT 
                                strftime('%Y-%m-%d %H:00:00', last_timestamp) as hour,
                                COUNT(*) as unique_count,
                                SUM(count) as total_count
                            FROM deduplicated_logs
                            WHERE last_timestamp >= datetime('now', '-24 hours')
                            GROUP BY hour
                            ORDER BY hour
                        `;

                        this.db.all(hourlyStatsSql, [], (err, hourlyStats) => {
                            if (err) {
                                console.error('获取去重日志小时统计失败:', err.message);
                                reject(err);
                                return;
                            }

                            resolve({
                                unique_total: totalResult.unique_total,
                                total: totalResult.total,
                                byLevel: levelStats,
                                byModule: moduleStats,
                                byHour: hourlyStats
                            });
                        });
                    });
                });
            });
        });
    }

    // 获取日志统计信息
    async getLogStats(filters = {}) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            let whereClause = 'WHERE 1=1';
            const params = [];
            
            if (filters.startTime) {
                whereClause += ' AND timestamp >= ?';
                params.push(filters.startTime);
            }
            
            if (filters.endTime) {
                whereClause += ' AND timestamp <= ?';
                params.push(filters.endTime);
            }

            // 按级别统计
            const levelStatsSql = `
                SELECT level, COUNT(*) as count
                FROM logs
                ${whereClause}
                GROUP BY level
                ORDER BY level
            `;

            this.db.all(levelStatsSql, params, (err, levelStats) => {
                if (err) {
                    console.error('获取级别统计失败:', err.message);
                    reject(err);
                    return;
                }

                // 按模块统计
                const moduleStatsSql = `
                    SELECT module, COUNT(*) as count
                    FROM logs
                    ${whereClause}
                    GROUP BY module
                    ORDER BY count DESC
                    LIMIT 10
                `;

                this.db.all(moduleStatsSql, params, (err, moduleStats) => {
                    if (err) {
                        console.error('获取模块统计失败:', err.message);
                        reject(err);
                        return;
                    }

                    // 总数统计
                    const totalSql = `
                        SELECT COUNT(*) as total
                        FROM logs
                        ${whereClause}
                    `;

                    this.db.get(totalSql, params, (err, totalResult) => {
                        if (err) {
                            console.error('获取总数统计失败:', err.message);
                            reject(err);
                            return;
                        }

                        // 按小时统计（最近24小时）
                        const hourlyStatsSql = `
                            SELECT 
                                strftime('%Y-%m-%d %H:00:00', timestamp) as hour,
                                COUNT(*) as count
                            FROM logs
                            WHERE timestamp >= datetime('now', '-24 hours')
                            GROUP BY hour
                            ORDER BY hour
                        `;

                        this.db.all(hourlyStatsSql, [], (err, hourlyStats) => {
                            if (err) {
                                console.error('获取小时统计失败:', err.message);
                                reject(err);
                                return;
                            }

                            resolve({
                                total: totalResult.total,
                                byLevel: levelStats,
                                byModule: moduleStats,
                                byHour: hourlyStats
                            });
                        });
                    });
                });
            });
        });
    }

    // 关闭数据库连接
    // 设备管理方法
    async createOrUpdateDevice(deviceId, deviceName, description = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const now = new Date().toISOString();
            
            // 先检查设备是否存在
            this.db.get('SELECT * FROM devices WHERE id = ?', [deviceId], (err, row) => {
                if (err) {
                    reject(err);
                    return;
                }
                
                if (row) {
                    // 更新现有设备
                    this.db.run(
                        'UPDATE devices SET name = ?, description = ?, last_connected = ?, is_active = 1 WHERE id = ?',
                        [deviceName, description, now, deviceId],
                        function(err) {
                            if (err) {
                                reject(err);
                                return;
                            }
                            resolve({ deviceId, isNew: false });
                        }
                    );
                } else {
                    // 创建新设备
                    this.db.run(
                        'INSERT INTO devices (id, name, description, first_connected, last_connected, is_active) VALUES (?, ?, ?, ?, ?, 1)',
                        [deviceId, deviceName, description, now, now],
                        function(err) {
                            if (err) {
                                reject(err);
                                return;
                            }
                            resolve({ deviceId, isNew: true });
                        }
                    );
                }
            });
        });
    }

    async getDevices(activeOnly = false) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            let sql = 'SELECT * FROM devices';
            const params = [];
            
            if (activeOnly) {
                sql += ' WHERE is_active = 1';
            }
            
            sql += ' ORDER BY last_connected DESC';
            
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                    return;
                }
                resolve(rows || []);
            });
        });
    }

    // 获取设备启动统计信息
    async getDeviceStartupStats(deviceId = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            let sql, params;
            
            if (deviceId) {
                // 获取特定设备的启动统计
                sql = `
                    SELECT 
                        d.id,
                        d.name,
                        d.first_connected,
                        d.last_connected,
                        COUNT(s.id) as total_sessions,
                        COUNT(s.id) as startup_count,
                        COUNT(CASE WHEN s.is_active = 1 THEN 1 END) as active_sessions,
                        (SELECT s2.id FROM sessions s2 WHERE s2.device_id = d.id AND s2.is_active = 1 LIMIT 1) as current_session_id
                    FROM devices d
                    LEFT JOIN sessions s ON d.id = s.device_id
                    WHERE d.id = ?
                    GROUP BY d.id
                `;
                params = [deviceId];
            } else {
                // 获取所有设备的启动统计
                sql = `
                    SELECT 
                        d.id,
                        d.name,
                        d.first_connected,
                        d.last_connected,
                        d.is_active,
                        COUNT(s.id) as total_sessions,
                        COUNT(s.id) as startup_count,
                        COUNT(CASE WHEN s.is_active = 1 THEN 1 END) as active_sessions,
                        (SELECT s2.id FROM sessions s2 WHERE s2.device_id = d.id AND s2.is_active = 1 LIMIT 1) as current_session_id
                    FROM devices d
                    LEFT JOIN sessions s ON d.id = s.device_id
                    GROUP BY d.id
                    ORDER BY COUNT(s.id) DESC, d.last_connected DESC
                `;
                params = [];
            }
            
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                    return;
                }
                
                if (deviceId) {
                    resolve(rows[0] || null);
                } else {
                    resolve(rows || []);
                }
            });
        });
    }

    // 会话管理方法
    async createSession(deviceId, sessionName = null) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const sessionId = uuidv4();
            const now = new Date().toISOString();
            const defaultSessionName = sessionName || `Session ${new Date().toLocaleString()}`;
            
            // 先将该设备的其他会话设为非活跃
            this.db.run(
                'UPDATE sessions SET is_active = 0, end_time = ? WHERE device_id = ? AND is_active = 1',
                [now, deviceId],
                (err) => {
                    if (err) {
                        reject(err);
                        return;
                    }
                    
                    // 创建新会话
                    this.db.run(
                        'INSERT INTO sessions (id, device_id, session_name, start_time, is_active) VALUES (?, ?, ?, ?, 1)',
                        [sessionId, deviceId, defaultSessionName, now],
                        function(err) {
                            if (err) {
                                reject(err);
                                return;
                            }
                            resolve({ sessionId, sessionName: defaultSessionName });
                        }
                    );
                }
            );
        });
    }

    async getDeviceSessions(deviceId, activeOnly = false) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            let sql = 'SELECT * FROM sessions WHERE device_id = ?';
            const params = [deviceId];
            
            if (activeOnly) {
                sql += ' AND is_active = 1';
            }
            
            sql += ' ORDER BY start_time DESC';
            
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                    return;
                }
                resolve(rows || []);
            });
        });
    }

    // 获取所有会话
    async getAllSessions() {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const sql = `
                SELECT 
                    s.*,
                    s.id as session_id,
                    d.name as device_name
                FROM sessions s
                LEFT JOIN devices d ON s.device_id = d.id
                ORDER BY s.start_time DESC
            `;
            
            this.db.all(sql, [], (err, rows) => {
                if (err) {
                    reject(err);
                    return;
                }
                resolve(rows || []);
            });
        });
    }

    // 删除设备及其相关数据
    async deleteDevice(deviceId) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION');
                
                // 删除设备相关的日志
                this.db.run('DELETE FROM logs WHERE device_id = ?', [deviceId], (err) => {
                    if (err) {
                        this.db.run('ROLLBACK');
                        reject(err);
                        return;
                    }
                    
                    // 删除设备相关的去重日志
                    this.db.run('DELETE FROM deduplicated_logs WHERE device_id = ?', [deviceId], (err) => {
                        if (err) {
                            this.db.run('ROLLBACK');
                            reject(err);
                            return;
                        }
                        
                        // 删除设备相关的会话
                        this.db.run('DELETE FROM sessions WHERE device_id = ?', [deviceId], (err) => {
                            if (err) {
                                this.db.run('ROLLBACK');
                                reject(err);
                                return;
                            }
                            
                            // 删除设备本身
                            this.db.run('DELETE FROM devices WHERE id = ?', [deviceId], (err) => {
                                if (err) {
                                    this.db.run('ROLLBACK');
                                    reject(err);
                                    return;
                                }
                                
                                this.db.run('COMMIT', (err) => {
                                    if (err) {
                                        reject(err);
                                    } else {
                                        console.log(`设备已删除: ${deviceId}`);
                                        resolve({ deviceId, deleted: true });
                                    }
                                });
                            });
                        });
                    });
                });
            });
        });
    }

    async endSession(sessionId) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            const now = new Date().toISOString();
            
            this.db.run(
                'UPDATE sessions SET is_active = 0, end_time = ? WHERE id = ?',
                [now, sessionId],
                function(err) {
                    if (err) {
                        reject(err);
                        return;
                    }
                    resolve({ sessionId, endTime: now });
                }
            );
        });
    }

    async updateSessionLogCount(sessionId, increment = 1) {
        if (!this.isInitialized) {
            throw new Error('数据库未初始化');
        }

        return new Promise((resolve, reject) => {
            this.db.run(
                'UPDATE sessions SET log_count = log_count + ? WHERE id = ?',
                [increment, sessionId],
                function(err) {
                    if (err) {
                        reject(err);
                        return;
                    }
                    resolve({ sessionId, updated: this.changes > 0 });
                }
            );
        });
    }

    async close() {
        if (this.db) {
            return new Promise((resolve, reject) => {
                this.db.close((err) => {
                    if (err) {
                        console.error('关闭数据库失败:', err.message);
                        reject(err);
                        return;
                    }
                    console.log('数据库连接已关闭');
                    this.isInitialized = false;
                    resolve();
                });
            });
        }
    }
}

module.exports = Database;